如何在Ubuntu 16.04上使用ProxySQL作为MySQL的负载均衡器

如何在Ubuntu 16.04上使用ProxySQL作为MySQL的负载均衡器
发表于2018年1月8日 73.9k 次查看MYSQL 负载平衡数据库群集高可用性
作者：Mateusz Papiernik

介绍

ProxySQL是一个开源的MySQL代理服务器，这意味着它充当MySQL服务器和访问其数据库的应用程序之间的中介。ProxySQL可以通过在多个数据库服务器池之间分配流量来提高性能，并且如果一个或多个数据库服务器发生故障，还可以通过自动故障切换到备用数据库来提高可用性。

在本指南中，您将设置ProxySQL作为具有自动故障转移功能的多个MySQL服务器的负载平衡器。例如，本教程使用由三个MySQL服务器组成的多主复制集群，但您也可以使用与其他集群配置类似的方法。

先决条件

要学习本教程，您需要：

一个Ubuntu 16.04服务器设置了这个初始的Ubuntu 16.04服务器设置教程，包括一个sudo非root用户和防火墙。该服务器将成为您的ProxySQL实例。
配置三个MySQL服务器以形成多主复制组。您可以按照Ubuntu 16.04上的如何配置MySQL组复制教程进行设置。在“ 选择单个主要”或“多个主要”部分中，按照多主复制组的说明进行操作。
第1步 - 安装ProxySQL
ProxySQL的开发人员在他们的GitHub版本页面上为所有ProxySQL版本提供官方Ubuntu软件包，因此我们将从那里下载最新的软件包版本并进行安装。

您可以在发布列表中找到最新的软件包。命名约定是，类似于版本1.4.4 的字符串，并且是类似于64位Ubuntu 16.04 的字符串。proxysql_version-distribution.debversion1.4.4distributionubuntu16_amd64

将最新的官方软件包（编写本文时为1.4.4）下载到/tmp目录中。

cd /tmp
curl -OL https://github.com/sysown/proxysql/releases/download/v1.4.4/proxysql_1.4.4-ubuntu16_amd64.deb

安装包dpkg，用于管理.deb软件包。该-i标志表示我们要从指定的文件安装。

sudo dpkg -i proxysql_*

此时，您不再需要该.deb文件，因此您可以将其删除。

rm proxysql_*

接下来，我们需要一个MySQL客户端应用程序来连接到ProxySQL实例。这是因为ProxySQL内部使用与MySQL兼容的接口来执行管理任务。我们将使用mysql命令行工具，它是mysql-clientUbuntu存储库中可用包的一部分。

更新软件包存储库以确保获得最新的预捆绑版本，然后安装mysql-client软件包。

sudo apt-get update
sudo apt-get install mysql-client

您现在拥有运行ProxySQL的所有要求，但该服务在安装后不会自动启动，因此请立即手动启动。

sudo systemctl start proxysql

现在，ProxySQL应该以其默认配置运行。你可以查看使用systemctl。

systemctl status proxysql

输出看起来类似于：

Output
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (running) since Thu 2017-12-21 19:19:20 UTC; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 12350 ExecStart=/etc/init.d/proxysql start (code=exited, status=0/SUCCESS)
    Tasks: 23
   Memory: 30.9M
      CPU: 86ms
   CGroup: /system.slice/proxysql.service
           ├─12355 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
           └─12356 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

该active (running)行表示ProxySQL已安装并正在运行。

接下来，我们将通过设置用于访问ProxySQL管理界面的密码来提高安全性。

第2步 - 设置ProxySQL管理员密码

第一次启动新的ProxySQL安装时，它使用包提供的配置文件来初始化其所有配置变量的默认值。初始化后，ProxySQL将其配置存储在数据库中，您可以通过命令行进行管理和修改。

要在ProxySQL中设置管理员密码，我们将连接到该配置数据库并更新相应的变量。

首先，访问管理界面。系统将提示您输入密码，在默认安装中，密码为admin。

mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

• -u指定我们要连接的用户，这里是admin，管理任务的默认用户，例如更改配置设置。
• -h 127.0.0.1告诉mysql连接到本地ProxySQL实例。我们需要明确定义它，因为ProxySQL不会监听mysql默认情况下假定的套接字文件。
• -P指定要连接的端口。ProxySQL的管理界面监听6032。

• --prompt是一个可选标志，用于更改默认提示，通常是这样mysql>。在这里，我们将其更改为ProxySQLAdmin>显示我们已连接到ProxySQL管理界面。这将有助于避免以后在我们也将连接到复制数据库服务器上的MySQL接口时出现混淆。

连接后，您将看到ProxySQLAdmin>提示：

ProxySQL administration console prompt
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

ProxySQLAdmin>

通过更新（UPDATE）数据库中的admin-admin_credentials配置变量来更改管理帐户密码global_variables。请记住将password以下命令更改为您选择的强密码。

UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials';
Output
Query OK, 1 row affected (0.00 sec)

由于ProxySQL的配置系统如何工作，此更改不会立即生效。它由三个独立的层组成：

内存，在从命令行界面进行修改时会被更改。
运行时，ProxySQL使用它作为有效配置。
disk，用于使配置在重新启动时保持不变。
现在，你所做的改变是在记忆中。要使更改生效，您必须将内存设置复制到运行时领域，然后将它们保存到磁盘以使其保持不变。

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

这些ADMIN命令仅处理与管理命令行界面相关的变量。ProxySQL公开类似的命令，例如MYSQL，处理其配置的其他部分。我们将在本教程后面使用它们。

现在ProxySQL已经安装并运行了新的管理员密码，让我们设置3个MySQL节点，以便ProxySQL可以监控它们。但是，请保持ProxySQL接口处于打开状态，因为我们稍后会使用它。

第3步 - 在MySQL中配置监控

ProxySQL需要与MySQL节点通信才能评​​估其状况。为此，它必须能够与专用用户连接到每个服务器。

在这里，我们将在MySQL节点上配置必要的用户并安装允许ProxySQL查询组复制状态的其他SQL函数。

由于MySQL组复制已在运行，因此必须仅对该组的单个成员执行以下步骤。

在第二个终端中，使用其中一个MySQL节点登录服务器。

ssh sammy@your_mysql_server_ip_1

下载包含一些必要功能的SQL文件，以便ProxySQL组复制支持工作。

curl -OL https://gist.github.com/lefred/77ddbde301c72535381ae7af9f968322/raw/5e40b03333a3c148b78aa348fd2cd5b5dbb36e4d/addition_to_sys.sql

注意：此文件由ProxySQL作者提供，但是以临时方式提供：它是个人GitHub存储库中的要点，这意味着它可能会移动或变得过时。将来，它可能会作为官方ProxySQL存储库中的版本化文件添加。

您可以在作者的博客文章中阅读有关此文件的上下文和内容的更多信息，内容涉及MySQL组复制的本机ProxySQL支持。

您可以使用查看文件的内容less addition_to_sys.sql。

准备好后，执行文件中的命令。系统将提示您输入MySQL管理密码。

mysql -u root -p < addition_to_sys.sql

如果命令成功运行，则不会产生任何输出。在这种情况下，所有MySQL节点现在将公开ProxySQL的必要功能，以识别组复制状态。

接下来，我们必须创建一个专用用户，ProxySQL将使用该用户来监视实例的运行状况。

打开MySQL交互式提示，它将再次提示您输入root密码。

mysql -u root -p

然后创建专用用户，我们在这里称为监视器。确保将密码更改为强密码。

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitorpassword';

授予用户权限以向监视器用户查询MySQL服务器的状况。

GRANT SELECT on sys.* to 'monitor'@'%';

最后，应用更改。

FLUSH PRIVILEGES;

由于组复制，一旦您将用于运行状况监视的用户添加到一个MySQL节点，它将在所有三个节点上完全配置。

接下来，我们需要使用该用户的信息更新ProxySQL，以便它可以访问MySQL节点。

步骤4 - 在ProxySQL中配置监控

要在监控节点时将ProxySQL配置为使用新用户帐户，我们将提供UPDATE相应的配置变量。这与我们从步骤2设置管理员密码的方式非常相似。

回到ProxySQL管理界面，将mysql-monitor_username变量更新为新帐户的用户名。

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

与以前一样，配置不会自动应用，因此将其迁移到运行时并保存到磁盘。这一次，请注意我们正在使用MYSQL而不是ADMIN更新这些变量，因为我们正在修改MySQL配置变量。

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

监控帐户在所有端都配置，下一步是告诉ProxySQL有关节点本身的信息。

步骤5 - 将MySQL节点添加到ProxySQL服务器池

为了让ProxySQL知道我们的三个MySQL节点，我们需要告诉ProxySQL如何在它们指定的节点集的主机组之间分发它们。每个主机组由正数标识，如1或2。使用ProxySQL查询路由时，主机组可以将不同的SQL查询路由到不同的主机集。

在静态复制配置中，可以任意设置主机组。但是，ProxySQL的组复制支持会自动将复制组中的所有节点划分为四种逻辑状态：

writers，它是可以接受更改数据的查询的MySQL节点。ProxySQL确保将所有主节点维持在此组中的最大定义数量。
备份编写器，也是可以接受更改数据的查询的MySQL节点。但是，这些节点不被指定为编写器; 超过定义的维护编写器数量的主节点保留在该组中，如果其中一个编写器失败，则被提升为编写器。
读者是MySQL节点，不能接受更改数据的查询，应该用作只读节点。ProxySQL只在此处放置从属节点。

离线，用于由于缺乏连接或流量缓慢等问题而导致行为不正常的节点。
这四种状态中的每一种都具有相应的主机组，但不会自动分配数字组标识符。

总而言之，我们需要告诉ProxySQL它应该为每个状态使用哪些标识符。在这里，我们使用1了离线主机组，2对作家主机组，3为读者主机组，并4为备份作家主机组。

要设置这些标识符，请在mysql_group_replication_hostgroups配置表中创建包含这些变量和值的新行。

INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) VALUES (2, 4, 3, 1, 1, 3, 1, 100);

这些是此行中设置的附加变量以及每个变量的作用：

active设置为1启用ProxySQL监视这些主机组。
max_writers定义有多少节点可以充当编写器。我们3在这里使用是因为在多主配置中，所有节点都可以被视为相等，所以我们在这里使用3（节点总数）。
writer_is_also_reader设置为1指示ProxySQL也将编写者视为读者。
max_transactions_behind设置节点被分类为脱机之前的最大延迟事务数。\

注：由于我们的示例使用在所有节点都可以写入到数据库中的多主拓扑中，我们将跨越平衡所有SQL查询作家主机组。在其他拓扑中，写入（主）节点和读取器（辅助）节点之间的划分可以将只读查询路由到不同于写入查询的节点/主机组。ProxySQL不会自动执行此操作，但您可以使用规则设置查询路由。

现在ProxySQL知道如何跨主机组分发节点，我们可以将MySQL服务器添加到池中。为此，我们需要将INSERT每个服务器的IP地址和初始主机组放入mysql_servers表中，其中包含ProxySQL可以与之交互的服务器列表。

添加三个MySQL服务器中的每一个，确保替换以下命令中的示例IP地址。

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.1', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.2', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.3', 3306);

这里，该2值最初将所有这些节点设置为编写器，并3306设置默认的MySQL端口。

与以前一样，将这些更改迁移到运行时并将其保存到磁盘以使更改生效。

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

ProxySQL现在应该按照指定在主机组中分布我们的节点。让我们通过SELECT对runtim330e_mysql_servers表执行查询来检查，该表公开了ProxySQL正在使用的服务器的当前状态。

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
Output
+--------------+-------------+--------+
| hostgroup_id | hostname    | status |
+--------------+-------------+--------+
| 2            | 203.0.113.1 | ONLINE |
| 2            | 203.0.113.2 | ONLINE |
| 2            | 203.0.113.3 | ONLINE |
| 3            | 203.0.113.1 | ONLINE |
| 3            | 203.0.113.2 | ONLINE |
| 3            | 203.0.113.3 | ONLINE |
+--------------+-------------+--------+
6 rows in set (0.01 sec)

在结果表中，每个服务器都列出两次：每个服务器一次列出主机组ID， 2并3指示所有三个节点都是编写器和读取器。所有节点都已标记ONLINE，这意味着它们已准备好使用。

但是，在我们使用它们之前，我们必须配置用户凭据以访问每个节点上的MySQL数据库。

第6步 - 创建MySQL用户

ProxySQL充当负载均衡器; 最终用户连接到ProxySQL，ProxySQL依次将连接传递给所选的MySQL节点。为了连接到单个节点，ProxySQL重用它所访问的凭据。

要允许访问位于复制节点上的数据库，我们需要创建一个与ProxySQL具有相同凭据的用户帐户，并为该用户授予必要的权限。

与步骤3中一样，必须仅对组中的单个成员执行以下步骤。您可以选择任何一个成员。

创建一个名为playgrounduser的新用户，该用户使用密码标识playgroundpassword。

CREATE USER 'playgrounduser'@'%' IDENTIFIED BY 'playgroundpassword';

授予其playground从原始组复制教程完全访问测试数据库的权限。

GRANT ALL PRIVILEGES on playground.* to 'playgrounduser'@'%';

然后应用更改并退出提示。

FLUSH PRIVILEGES;
EXIT;

您可以通过直接在节点上尝试使用新配置的凭据来验证用户是否已正确创建。

使用新用户重新打开MySQL界面，这将提示您输入密码。

mysql -u playgrounduser -p

登录后，对playground数据库执行测试查询。

SHOW TABLES FROM playground;
Output
+----------------------+
| Tables_in_playground |
+----------------------+
| equipment            |
+----------------------+
1 row in set (0.00 sec)

数据库中可见的equipment表列表显示了在原始复制教程中创建的表，确认用户已在节点上正确创建。

您现在可以断开与MySQL接口的连接，但要保持终端与服务器的连接打开。我们将在最后一步中使用它来运行测试。

EXIT;

现在我们需要在ProxySQL服务器中创建相应的用户。

第7步 - 创建ProxySQL用户

最后的配置步骤是允许与playgrounduser用户连接到ProxySQL ，并将这些连接传递给节点。

为此，我们需要在mysql_users表中设置配置变量，其中包含用户凭据信息。在ProxySQL接口，添加用户名，密码，和默认主机组的配置数据库（其是2，对于作家主机组）

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('playgrounduser', 'playgroundpassword', 2);

将配置迁移到运行时并保存到磁盘以使新配置生效。

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

要验证我们是否可以使用这些凭据连接到数据库节点，请打开另一个终端窗口并通过SSH连接到ProxySQL服务器。我们以后仍然需要管理提示，所以不要关闭它。

ssh sammy@your_proxysql_server_ip

ProxySQL在端口上侦听6033传入的客户端连接，因此请尝试使用playgrounduser和port 连接到真实数据库（而不是管理界面）6033。系统将提示您输入密码，这playgroundpassword在我们的示例中。

mysql -u playgrounduser -p -h 127.0.0.1 -P 6033 --prompt='ProxySQLClient> '

在这里，我们设置提示，ProxySQLClient>以便我们可以将其与管理界面提示区分开来。我们将在测试最终配置时使用它们。

提示应该打开，这意味着ProxySQL本身已接受凭据。

ProxySQL client prompt
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

ProxySQLClient>

让我们执行一个简单的语句来验证ProxySQL是否会连接到其中一个节点。此命令在数据库中查询其运行的服务器的主机名，并返回服务器主机名作为唯一输出。

SELECT @@hostname;

根据我们的配置中，这个查询应该由ProxySQL被引导到我们分配到三个节点的一个作家主机组。输出应如下所示，其中member1一个MySQL节点的主机名。

Output
+------------+
| @@hostname |
+------------+
| member1    |
+------------+
1 row in set (0.00 sec)

这样就完成了允许ProxySQL在三个MySQL节点之间加载平衡连接的配置。

在最后一步中，我们将验证ProxySQL是否可以在数据库上执行读写语句，并且即使某些节点出现故障也会处理查询。

步骤8 - 验证ProxySQL配置

我们知道ProxySQL和MySQL节点之间的连接正在工作，因此最终的测试是确保数据库权限允许来自ProxySQL的读写语句，并确保在某些节点中仍然执行这些语句小组失败了。

SELECT在ProxySQL客户端提示符中执行语句以验证我们是否可以从playground数据库中读取数据。

SELECT * FROM playground.equipment;

输出应类似于以下内容，包含在组复制教程中创建的三个项目。这意味着我们通过ProxySQL成功地从MySQL数据库中读取数据。

Output
+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  3 | slide  |     2 | blue   |
| 10 | swing  |    10 | yellow |
| 17 | seesaw |     3 | green  |
+----+--------+-------+--------+
3 rows in set (0.00 sec)

接下来，尝试通过在代表5个红钻的表中插入一些新数据来编写。

INSERT INTO playground.equipment (type, quant, color) VALUES ("drill", 5, "red");

然后重新执行上一个SELECT命令以验证是否已插入数据。

SELECT * FROM playground.equipment;

输出中的新钻取线意味着我们通过ProxySQL成功地将数据写入MySQL数据库。

Output
+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  3 | slide  |     2 | blue   |
| 10 | swing  |    10 | yellow |
| 17 | seesaw |     3 | green  |
| 24 | drill  |     5 | red    |
+----+--------+-------+--------+
4 rows in set (0.00 sec)

我们知道ProxySQL现在可以完全使用数据库，但是如果服务器出现故障会怎么样？

从其中一个MySQL服务器的命令行，停止MySQL进程以模拟故障。

systemctl stop mysql

数据库停止后，尝试equipment再次从ProxySQL客户端提示符中查询表中的数据。

SELECT * FROM playground.equipment;

输出不应改变; 你仍然应该像以前一样看到设备清单。这意味着ProxySQL注意到其中一个节点出现故障并切换到另一个节点以执行该语句。

我们可以通过runtime_mysql_servers从ProxySQL管理提示中查询表来检查，如步骤5中所示。

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

输出将如下所示：

Output
+--------------+-------------+---------+
| hostgroup_id | hostname    | status  |
+--------------+-------------+---------+
| 1            | 203.0.113.1 | SHUNNED |
| 2            | 203.0.113.2 | ONLINE  |
| 2            | 203.0.113.3 | ONLINE  |
| 3            | 203.0.113.2 | ONLINE  |
| 3            | 203.0.113.3 | ONLINE  |
+--------------+-------------+---------+
6 rows in set (0.01 sec)

我们停止的节点现在被避开，这意味着它暂时被认为是无法访问的，因此所有流量将分布在剩余的两个在线节点上。

ProxySQL将持续监视此节点的状态，如果行为正常则将其恢复为联机状态，或者如果超过我们在步骤4中设置的超时阈值，则将其标记为脱机。

我们来测试这个监控。切换回MySQL服务器并重新启动节点。

systemctl start mysql

稍等片刻，然后runtime_mysql_servers再次从ProxySQL管理提示中查询该表。

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

ProxySQL会很快注意到该节点再次可用并将其标记为在线：

Output
+--------------+-------------+--------+
| hostgroup_id | hostname    | status |
+--------------+-------------+--------+
| 2            | 203.0.113.1 | ONLINE |
| 2            | 203.0.113.2 | ONLINE |
| 2            | 203.0.113.3 | ONLINE |
| 3            | 203.0.113.1 | ONLINE |
| 3            | 203.0.113.2 | ONLINE |
| 3            | 203.0.113.3 | ONLINE |
+--------------+-------------+--------+
6 rows in set (0.01 sec)

您可以使用另一个节点（或其中两个节点）重复此测试，以确定如果至少有一个节点启动，您将能够自由地将数据库用于只读和读写访问。

结论

在本教程中，您将ProxySQL配置为在多主组复制拓扑中跨多个启用写入的MySQL节点对SQL查询进行负载平衡。这种配置可以通过在多个服务器之间分配负载来提高大量数据库使用的性能。它还可以在其中一个数据库服务器脱机时提供故障转移功能。

但是，我们这里仅以一个节点拓扑为例。ProxySQL还为许多其他MySQL拓扑提供了强大的查询缓存，路由和性能分析。您可以在官方ProxySQL博客和ProxySQL wiki上阅读有关ProxySQL功能以及如何解决不同数据库管理问题的更多信息。

翻译：Yujiaao